3.07. Общие табличные выражения

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Общие табличные выражения

Определение и назначение

Общее табличное выражение представляет собой именованное временное результирующее множество, существующее в рамках одного SQL-запроса. Общие табличные выражения позволяют разбить сложный запрос на логически связанные части, каждая из которых имеет собственное имя и может использоваться в последующих частях запроса.

Основное назначение общих табличных выражений заключается в повышении читаемости и поддерживаемости сложных SQL-запросов. Они предоставляют возможность выделить промежуточные вычисления, дать им осмысленные имена и использовать эти именованные результаты в основном запросе или в других общих табличных выражениях.

Общие табличные выражения действуют как временные представления, существующие только в течение выполнения одного запроса. После завершения выполнения запроса общее табличное выражение автоматически удаляется, и его результаты становятся недоступными.

Синтаксис и структура

Общее табличное выражение определяется с использованием ключевого слова WITH, за которым следует имя выражения, список столбцов в круглых скобках и ключевое слово AS. После этого в круглых скобках указывается полный подзапрос, результат которого будет доступен под заданным именем.

WITH имя_выражения (столбец1, столбец2, столбец3) AS (
    SELECT столбец1, столбец2, столбец3
    FROM таблица
    WHERE условие
)
SELECT * FROM имя_выражения;

Имя общего табличного выражения должно быть уникальным в рамках запроса и не должно совпадать с именами существующих таблиц или представлений в базе данных. Список столбцов в определении является необязательным, если имена столбцов могут быть однозначно определены из подзапроса.

Общее табличное выражение определяется перед основным запросом и доступно только в этом запросе. Оно не может быть использовано в других запросах, даже если они выполняются в рамках одной транзакции.

Возможности операций выборки

Общее табличное выражение может содержать любые операции выборки, доступные в SQL. Это включает соединения таблиц, агрегатные функции, оконные функции, подзапросы и другие сложные конструкции.

Пример использования соединений в общем табличном выражении:

WITH заказы_клиентов AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        o.order_id,
        o.order_date,
        o.total_amount
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2025-01-01'
)
SELECT 
    customer_name,
    COUNT(order_id) AS количество_заказов,
    SUM(total_amount) AS общая_сумма
FROM заказы_клиентов
GROUP BY customer_name
ORDER BY общая_сумма DESC;

Пример с использованием агрегатных функций:

WITH продажи_по_регионам AS (
    SELECT 
        region,
        SUM(sales_amount) AS total_sales,
        AVG(sales_amount) AS avg_sales,
        COUNT(*) AS transaction_count
    FROM sales_data
    GROUP BY region
)
SELECT 
    region,
    total_sales,
    avg_sales,
    transaction_count,
    total_sales / transaction_count AS средний_чек
FROM продажи_по_регионам
WHERE total_sales > 100000;

Пример с оконными функциями:

WITH рейтинги_продуктов AS (
    SELECT 
        product_id,
        product_name,
        category,
        sales_amount,
        RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank,
        ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS global_rank
    FROM products
    WHERE active = 1
)
SELECT 
    product_name,
    category,
    sales_amount,
    category_rank,
    global_rank
FROM рейтинги_продуктов
WHERE category_rank <= 3;

Множественные последовательные выражения

Общие табличные выражения поддерживают определение нескольких последовательных или взаимосвязанных выражений через запятую. Каждое последующее выражение может ссылаться на предыдущие выражения, определенные в том же блоке WITH.

WITH базовые_данные AS (
    SELECT 
        employee_id,
        employee_name,
        department_id,
        salary,
        hire_date
    FROM employees
    WHERE status = 'active'
),
данные_отделов AS (
    SELECT 
        d.department_id,
        d.department_name,
        d.manager_id,
        COUNT(b.employee_id) AS employee_count
    FROM departments d
    LEFT JOIN базовые_данные b ON d.department_id = b.department_id
    GROUP BY d.department_id, d.department_name, d.manager_id
),
расчеты_зарплат AS (
    SELECT 
        b.employee_id,
        b.employee_name,
        b.department_id,
        b.salary,
        AVG(b.salary) OVER (PARTITION BY b.department_id) AS avg_dept_salary,
        b.salary - AVG(b.salary) OVER (PARTITION BY b.department_id) AS diff_from_avg
    FROM базовые_данные b
)
SELECT 
    r.employee_name,
    d.department_name,
    r.salary,
    r.avg_dept_salary,
    r.diff_from_avg,
    d.employee_count
FROM расчеты_зарплат r
JOIN данные_отделов d ON r.department_id = d.department_id
ORDER BY r.diff_from_avg DESC;

В этом примере каждое последующее общее табличное выражение использует результаты предыдущих выражений, создавая цепочку зависимостей. Это позволяет построить сложную логику обработки данных, разбив ее на понятные и управляемые части.

Рекурсивные общие табличные выражения

Для работы с иерархическими или древовидными данными используется рекурсивный вариант общего табличного выражения с ключевым словом RECURSIVE. Рекурсивное общее табличное выражение состоит из двух частей: начального запроса и рекурсивного запроса, разделенных оператором UNION ALL.

WITH RECURSIVE иерархия_отделов AS (
    -- Начальный запрос: корневые элементы
    SELECT 
        department_id,
        department_name,
        parent_department_id,
        department_name AS full_path,
        1 AS level
    FROM departments
    WHERE parent_department_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный запрос: дочерние элементы
    SELECT 
        d.department_id,
        d.department_name,
        d.parent_department_id,
        CONCAT(h.full_path, ' -> ', d.department_name) AS full_path,
        h.level + 1 AS level
    FROM departments d
    INNER JOIN иерархия_отделов h ON d.parent_department_id = h.department_id
)
SELECT 
    department_id,
    department_name,
    full_path,
    level
FROM иерархия_отделов
ORDER BY full_path;

Рекурсивные общие табличные выражения особенно полезны для работы с организационными структурами, категориями товаров, древовидными меню и другими данными, имеющими иерархическую природу.

Пример работы с древовидной структурой категорий:

WITH RECURSIVE категории_дерево AS (
    SELECT 
        category_id,
        category_name,
        parent_category_id,
        category_name AS path,
        0 AS depth
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.category_id,
        c.category_name,
        c.parent_category_id,
        CONCAT(ct.path, ' > ', c.category_name) AS path,
        ct.depth + 1 AS depth
    FROM categories c
    INNER JOIN категории_дерево ct ON c.parent_category_id = ct.category_id
)
SELECT 
    category_id,
    REPEAT('  ', depth) || category_name AS отступ_категория,
    path,
    depth
FROM категории_дерево
ORDER BY path;

Поддержка в различных СУБД

Общие табличные выражения поддерживаются в большинстве современных систем управления базами данных. В разных СУБД могут существовать незначительные различия в синтаксисе и функциональности.

PostgreSQL предоставляет полную поддержку общих табличных выражений, включая рекурсивные выражения. PostgreSQL поддерживает определение нескольких общих табличных выражений в одном запросе и позволяет использовать их в различных частях запроса.

MySQL начиная с версии 8.0 предоставляет поддержку общих табличных выражений. В более ранних версиях MySQL эта функциональность отсутствует.

SQL Server поддерживает общие табличные выражения начиная с версии 2005. SQL Server предоставляет дополнительные возможности, такие как использование общих табличных выражений в инструкциях INSERT, UPDATE и DELETE.

Oracle Database поддерживает общие табличные выражения начиная с версии 9i. Oracle предоставляет расширенные возможности для работы с рекурсивными общими табличными выражениями, включая управление циклами и ограничениями рекурсии.

SQLite поддерживает общие табличные выражения начиная с версии 3.8.3. Поддержка рекурсивных общих табличных выражений также доступна в SQLite.

Производительность и оптимизация

Производительность общих табличных выражений зависит от конкретной реализации в системе управления базами данных. В некоторых СУБД общие табличные выражения обрабатываются как встроенные представления, а в других они могут материализовываться во временные таблицы.

Оптимизатор запросов в большинстве современных СУБД способен эффективно обрабатывать общие табличные выражения, применяя те же методы оптимизации, что и для обычных подзапросов. Однако в некоторых случаях использование общих табличных выражений может привести к снижению производительности, особенно при работе с большими объемами данных.

Для оптимизации производительности общих табличных выражений рекомендуется использовать индексы на столбцах, используемых в условиях соединения и фильтрации. Также важно избегать избыточных вычислений и выбирать только необходимые столбцы в общих табличных выражениях.

Пример оптимизированного общего табличного выражения с использованием индексов:

WITH активные_пользователи AS (
    SELECT 
        user_id,
        username,
        registration_date,
        last_login_date
    FROM users
    WHERE status = 'active'
    AND last_login_date >= CURRENT_DATE - INTERVAL '30 days'
    ORDER BY last_login_date DESC
    LIMIT 1000
),
покупки_пользователей AS (
    SELECT 
        p.user_id,
        COUNT(*) AS purchase_count,
        SUM(p.amount) AS total_spent,
        MAX(p.purchase_date) AS last_purchase_date
    FROM purchases p
    WHERE p.purchase_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY p.user_id
)
SELECT 
    au.user_id,
    au.username,
    au.registration_date,
    pu.purchase_count,
    pu.total_spent,
    pu.last_purchase_date
FROM активные_пользователи au
LEFT JOIN покупки_пользователей pu ON au.user_id = pu.user_id
ORDER BY pu.total_spent DESC NULLS LAST;

В этом примере используются ограничения LIMIT и временные фильтры для уменьшения объема обрабатываемых данных. Индексы на столбцах status, last_login_date и purchase_date значительно ускоряют выполнение запроса.

Материализация и повторное использование

Некоторые системы управления базами данными предоставляют возможность материализации общих табличных выражений, что означает сохранение результатов выражения во временную таблицу для повторного использования. Это может значительно повысить производительность при многократном обращении к одному и тому же общему табличному выражению в рамках запроса.

В системах, поддерживающих материализацию, оптимизатор запросов автоматически решает, следует ли материализовать общее табличное выражение или обрабатывать его как встроенное представление. Решение принимается на основе анализа стоимости выполнения запроса и ожидаемого объема данных.

Пример запроса с потенциальной материализацией:

WITH сложные_вычисления AS (
    SELECT 
        product_id,
        product_name,
        category_id,
        base_price,
        (base_price * 1.2) AS price_with_tax,
        (base_price * 0.85) AS discounted_price,
        CASE 
            WHEN stock_quantity > 100 THEN 'high'
            WHEN stock_quantity > 50 THEN 'medium'
            ELSE 'low'
        END AS stock_level
    FROM products
    WHERE active = 1
    AND discontinued = 0
)
SELECT 
    sc.product_name,
    c.category_name,
    sc.price_with_tax,
    sc.discounted_price,
    sc.stock_level
FROM сложные_вычисления sc
JOIN categories c ON sc.category_id = c.category_id
WHERE sc.stock_level = 'high'
UNION ALL
SELECT 
    sc.product_name,
    c.category_name,
    sc.price_with_tax,
    sc.discounted_price,
    sc.stock_level
FROM сложные_вычисления sc
JOIN categories c ON sc.category_id = c.category_id
WHERE sc.price_with_tax > 1000
ORDER BY product_name;

В этом примере общее табличное выражение используется дважды в основном запросе. Система управления базами данных может выбрать материализацию этого выражения для избежания повторного выполнения сложных вычислений.

Взаимосвязанные общие табличные выражения

Взаимосвязанные общие табличные выражения позволяют создавать сложные цепочки зависимостей между различными частями запроса. Каждое выражение может ссылаться на предыдущие выражения, создавая многоуровневую структуру обработки данных.

WITH исходные_данные AS (
    SELECT 
        transaction_id,
        customer_id,
        transaction_date,
        amount,
        transaction_type
    FROM transactions
    WHERE transaction_date >= '2025-01-01'
),
агрегированные_данные AS (
    SELECT 
        customer_id,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_amount,
        MIN(transaction_date) AS first_transaction,
        MAX(transaction_date) AS last_transaction
    FROM исходные_данные
    GROUP BY customer_id
),
сегментация_клиентов AS (
    SELECT 
        ad.customer_id,
        ad.transaction_count,
        ad.total_amount,
        ad.avg_amount,
        ad.first_transaction,
        ad.last_transaction,
        CASE 
            WHEN ad.total_amount > 10000 THEN 'premium'
            WHEN ad.total_amount > 5000 THEN 'gold'
            WHEN ad.total_amount > 1000 THEN 'silver'
            ELSE 'bronze'
        END AS customer_segment
    FROM агрегированные_данные ad
),
статистика_сегментов AS (
    SELECT 
        customer_segment,
        COUNT(*) AS customer_count,
        SUM(total_amount) AS segment_total,
        AVG(total_amount) AS segment_avg,
        MIN(transaction_count) AS min_transactions,
        MAX(transaction_count) AS max_transactions
    FROM сегментация_клиентов
    GROUP BY customer_segment
)
SELECT 
    sc.customer_id,
    c.customer_name,
    c.email,
    sc.transaction_count,
    sc.total_amount,
    sc.avg_amount,
    sc.customer_segment,
    ss.segment_total,
    ss.segment_avg
FROM сегментация_клиентов sc
JOIN customers c ON sc.customer_id = c.customer_id
JOIN статистика_сегментов ss ON sc.customer_segment = ss.customer_segment
ORDER BY sc.total_amount DESC;

Этот пример демонстрирует создание многоуровневой структуры обработки данных, где каждое последующее общее табличное выражение строится на результатах предыдущих выражений. Такой подход позволяет создавать сложные аналитические запросы с четкой структурой и хорошей читаемостью.

Рекурсивные выражения с ограничениями

Рекурсивные общие табличные выражения могут включать ограничения для предотвращения бесконечной рекурсии. Ограничения могут быть заданы с помощью условия в рекурсивной части запроса или с использованием специальных ключевых слов, предоставляемых системой управления базами данных.

WITH RECURSIVE дерево_категорий AS (
    SELECT 
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.category_id,
        c.category_name,
        c.parent_category_id,
        CONCAT(dc.full_path, ' > ', c.category_name) AS full_path,
        dc.level + 1 AS level
    FROM categories c
    INNER JOIN дерево_категорий dc ON c.parent_category_id = dc.category_id
    WHERE dc.level < 10
)
SELECT 
    category_id,
    category_name,
    full_path,
    level
FROM дерево_категорий
ORDER BY full_path;

В этом примере ограничение dc.level < 10 предотвращает бесконечную рекурсию, ограничивая глубину обхода дерева категорий десятью уровнями. Это особенно важно при работе с данными, которые могут содержать циклические зависимости или очень глубокие иерархии.

Некоторые системы управления базами данных предоставляют дополнительные механизмы для управления рекурсией, такие как ключевые слова SEARCH и CYCLE, которые позволяют явно указывать порядок обхода и обнаруживать циклы в данных.

---